<?php

/**
 * Core_Database
 *
 * Wrapper class for preparing and executing MySQL-queries and fetching results
 *
 * @author Joona Somerkivi <joona.somerkivi@gmail.com>
 */
class Core_Database extends Core_Logger {
    const Type_Int = 'integer';
    const Type_Float = 'double';
    const Type_String = 'string';

    protected static $logTarget = '/tmp/query.log';
    //put your code here
    private $linkId;
    private $unpreparedQuery = null;
    private $resourceHandle = null;
    private $latestRow;
    private $rowCount = null;
    private $affectedRows = null;
    private $insertId = null;
    private $variables = array();
    private $rules = array();

    /**
     * Initialize a DB connection
     * @param $server URL of the MySQL server
     * @param $username Username to use in login
     * @param $password Password to use in login, null if no password used
     * @param $newLink Boolean
     */
    public static function init($server = 'localhost', $username = 'root', $password = null, $database = null, $newLink = false) {
        $db = new Core_Database();
        $db->connect($server, $username, $password, $database, $newLink);
        return $db;
    }

    /**
     * Connect to a MySQL server
     * @param $server URL of the MySQL server
     * @param $username Username to use in login
     * @param $password Password to use in login, null if no password used
     * @param $newLink Boolean
     */
    public function connect($server = 'localhost', $username = 'root', $password = null, $database = null, $newLink = false) {
        self::initLogger();
        $this->linkId = @mysql_connect($server, $username, $password, $newLink);
        if ($this->linkId === false)
            throw new Core_Database_Exception(
                    'Unable to connect to MySQL server: ' . mysql_error());
        if (!is_null($database)
            );
        $this->selectDatabase($database);
    }

    /**
     * Select database to use
     * @param String $database Name of the database to use
     * @throws Core_Database_Exception if database is not found
     */
    public function selectDatabase($database) {
        if (!mysql_select_db($database, $this->linkId))
            throw new Core_Database_Exception(
                    'Unable to select Database: ' . mysql_error());
    }

    /**
     * Set validation/format rules to multiple variables
     * $rules are given in format:
     * array(
     *      'nameOfVar' => array('type' => ... , 'regexp' => ... ),
     *      'anotherVar' => array('type' => ... , 'regexp' => ... )
     * )
     * @param $rules Array of variableName - rules pairs
     */
    public function setRules($rules) {
        foreach ($rules as $name => $rule)
            $this->setRule($name, $rule);
    }

    /**
     * Set validation/format rules to a variable
     *
     * Supported rules are:
     *      type, allowNull, minLength, maxLength, minValue, maxValue, regexp
     * For example:
     * $db->setRule('userID', array(
     *      'type' => 'integer', //integer, double, string, bool
     *      'minValue' => 1
     * ));
     *
     * @param $name Name of the variable rules apply to
     * @param $rule Array of validation/format rules
     */
    public function setRule($name, $rule) {
        $this->rules[$name] = $rule;
    }

    /**
     * Remove all rules from database handler
     */
    public function resetRules() {
        $this->rules = array();
    }

    /**
     * Remove all variables from database handler
     */
    public function resetVariables() {
        $this->variables = array();
    }

    /**
     * Set a varible to be inserted into query on execute()
     *
     * @param $name Name of the variable responding to {name} tag in query
     * @param $value Value to insert
     * @param $rule Array of validation/format rules, optional
     * @throws Core_Database_Exception if no rule for given variable is found
     */
    public function setVariable($name, $value, $rule = null) {
        if (!is_null($rule))
            $this->setRule($name, $rule);

        if (!isset($this->rules[$name]))
            throw new Core_Database_Exception("Unable to set variable '$name', missing rule");

        $this->variables[$name] = $value;
    }

    /**
     * Alias for setVariable()
     * @param String $name
     * @param Misc $value
     * @param Array $rule
     * @return void
     */
    public function setParameter($name, $value, $rule = null) {
        return $this->setVariable($name, $value, $rule);
    }

    /**
     * Set various variables at once
     * Variables must be given as list of array( name => value )
     * @param Array $listOfVariables
     */
    public function setVariables($listOfVariables) {
        foreach ($listOfVariables as $name => $value)
            $this->setVariable($name, $value);
    }

    /**
     * Bind a varible to be inserted into query on execute()
     *
     * @param $name Name of the variable responding to {name} tag in query
     * @param $value Reference to a variable which value is to be inserted
     * @param $rule Array of validation/format rules, optional
     * @throws Core_Database_Exception if no rule for given variable is found
     */
    public function bindVariable($name, &$value, $rule = null) {
        if (!is_null($rule))
            $this->setRule($name, $rule);

        if (!isset($this->rules[$name]))
            throw new Core_Database_Exception("Unable to bind variable '$name', missing rule");

        $this->variables[$name] = $value;
    }

    /**
     * Prepare a SQL-clause for execution
     * Variables should be tagged with {variableName} tags
     * @param $sqlClause SQL-clause to be prepared
     */
    public function prepare($sqlClause) {
        $this->unpreparedQuery = $sqlClause;
    }

    /**
     * Executes the query set with prepare()
     * @throws Core_Database_Exception in case of internal error
     * @throws Core_Database_MysqlException in case of mysql-error
     */
    public function execute() {
        if (!$this->isReady())
            throw new Core_Database_Exception('Not ready to execute');
        $this->validateVariables();
        $preparedQuery = $this->reformatQuery($this->unpreparedQuery);
        $preparedQuery = rtrim($preparedQuery, ';') . ';';

        if (!$this->isReadyForExecution($preparedQuery))
            throw new Core_Database_Exception('Query has unfilled variables:' . $preparedQuery);
        $result = mysql_query($preparedQuery, $this->linkId);

        if ($result === false) {
            self::log(mysql_error($this->linkId) . " from:\n" . $preparedQuery,
                            Core_Registry::get('QUERY_ERROR_LOG'));
            throw new Core_Database_MysqlException(mysql_error($this->linkId));
        } else if (Core_Registry::get('DEBUG'))
            self::log($preparedQuery, Core_Registry::get('QUERY_LOG'));

        $this->resourceHandle = $result;
    }

    /**
     * Checks that query doesn't have unprepared variables left
     * @param $query Prepared query to check
     * @return Bool
     */
    private function isReadyForExecution($query) {
        return!preg_match('/\{[A-Za-z]+\}/', $query);
    }

    /**
     * Get the row-count of previous SELECT-query
     * @return INT Number or rows in result set
     */
    public function getRowCount() {
        return mysql_num_rows($this->resourceHandle);
    }

    /**
     * Get the count of affected rows of previous INSERT/UPDATE-query
     * @return INT Number of affected rows
     */
    public function getAffectedRows() {
        return mysql_affected_rows($this->linkId);
    }

    /**
     * Get the ID of previous inserted row
     * @return INT
     */
    public function insertId() {
        return mysql_insert_id($this->linkId);
    }

    /**
     * Fetch single as array row from result-set
     * @return Array
     */
    public function fetch() {
        $this->latestRow = mysql_fetch_assoc($this->resourceHandle);
        if (is_array($this->latestRow))
            return true;
        else
            return false;
    }

    /**
     * Return all rows on the result-set
     * @return Array
     */
    public function getAllResults() {
        $buffer = array();
        while ($this->fetch())
            $buffer[] = $this->latestRow;
        return $buffer;
    }

    /**
     * Return a value from result-set, if no $key is provided, whole row is returned
     * 
     * @param string/null $key Index of latest row to return
     * @return MISC
     */
    public function getResult($key = null) {
        if (!is_null($key)) {
            if (!key_exists($key, $this->latestRow))
                throw new Core_Database_Exception("No key '$key' in result: " .
                        implode(', ', array_keys($this->latestRow)));
            else
                return $this->latestRow[$key];
        }
        else {
            return $this->latestRow;
        }
    }

    /**
     * Checks if the class is ready to execute query
     * @return Bool
     */
    public function isReady() {
        if (!$this->queryIsSet()) {
            self::log('Not ready, missing query');
            return false;
        }
        if (!$this->connectedToDatabase()) {
            self::log('Not ready, missing connection');
            return false;
        }
        return true;
    }

    /**
     * Checks that the connection is up
     * @return Bool
     */
    private function connectedToDatabase() {
        return!is_null($this->linkId);
    }

    /**
     * Checks that the class has a query to reformat
     * @return Bool
     */
    private function queryIsSet() {
        return strlen($this->unpreparedQuery) > 1;
    }

    /**
     * Checks that all set/binded variables meet validation rules
     * @return Bool
     * @throws Core_Database_Exception if validation fails
     */
    private function validateVariables() {
        if (!is_array($this->variables))
            return true;
        foreach ($this->variables as $name => $value) 
        {
            if(!Core_Validator::validateAgainst($value, $this->rules[$name], $name))
                    throw new Core_Database_ValidationException(
                            'Validation failed: '.  implode(', ',
                                    Core_Validator::getErrors()));
        }
        return true;
    }

    /**
     * Replaces variables from query with escaped values
     * @param $query SQL query to prepare
     * @return String reformatted query
     */
    private function reformatQuery($query) {
        list($keywords, $values) =
                $this->escapeKeywordsAndValues($this->isSelectClause($query));
        $preformatted = str_replace($keywords, $values, $query);
        if ($this->isSelectClause($query))
            $preformatted = $this->fixIsNulls($preformatted);
        return $preformatted;
    }

    /**
     * Replaces possible "= IS NULL" parts from a query
     * @param $query SQL query to check/fix
     * @return String fixed query
     */
    private function fixIsNulls($query) {
        return preg_replace('/=\ IS\ NULL/i', 'IS NULL', $query);
    }

    /**
     * Escapes all set/binded variables
     * Returns an array containing list of keys and list of values to be used with
     * str_replace
     * array( 0 => listOfSetKeys, 1 => listOfSetValues)
     * @return Array List of keys/values
     */
    private function escapeKeywordsAndValues($isSelectClause = false) {
        $keywords = array();
        $values = array();
        foreach ($this->variables as $key => $value) {
            $keywords[] = '{' . $key . '}';

            if (is_string($value))
                $values[] = "'" . mysql_real_escape_string($value, $this->linkId) . "'";
            else if (is_null($value) && $isSelectClause)
                $values[] = 'IS NULL';
            else if (is_null($value) && !$isSelectClause)
                $values[] = 'NULL';
            else
                $values[] = mysql_real_escape_string($value, $this->linkId);
        }
        return array($keywords, $values);
    }

    /**
     * Checks if prepared query is a SELECT clause or not
     * @return Bool
     */
    private function isSelectClause($query) {
        return (bool) preg_match('/^select/i', $query);
    }

}

class Core_Database_Exception extends Core_Exception {

}

class Core_Database_ValidationException extends Core_Database_Exception {

}

class Core_Database_MysqlException extends Core_Database_Exception {

}